package org.groupg.practice.json;


import org.apache.calcite.adapter.file.JsonScannableTable;
import org.apache.calcite.jdbc.CalciteConnection;
import org.apache.calcite.schema.SchemaPlus;
import org.apache.calcite.schema.Table;
import org.apache.calcite.schema.impl.AbstractSchema;
import org.apache.calcite.util.Sources;

import java.sql.*;
import java.util.*;

public class CalciteJsonTableExample {

    public static void main(String[] args) throws Exception {
        // 1. 初始化 Calcite 连接
        Properties properties = new Properties();
        Connection connection = DriverManager.getConnection("jdbc:calcite:", properties);
        CalciteConnection calciteConnection = connection.unwrap(CalciteConnection.class);
        SchemaPlus rootSchema = calciteConnection.getRootSchema();

        // 2. 将 JSON Schema 添加到 Calcite
        String jsonContent = """
                {
                                "adminList":
                                              [
                                                      {"ID": 3, "NAME": "Charlie", "AGE": 35,"CHILD":[
                                                          {"NAME":"1"}
                                                          ,{"NAME":"2"}
                                                      ]},
                                                      {"ID": 3, "NAME": "Charlie", "AGE": 35,"CHILD":[
                                                          {"NAME":"1"}
                                                          ,{"NAME":"2"}
                                                      ]},
                                                      {"ID": 3, "NAME": "Charlie", "AGE": 35,"CHILD":[
                                                          {"NAME":"1"}
                                                          ,{"NAME":"2"}
                                                      ]},
                                                      {"ID": 3, "NAME": "Charlie", "AGE": 35,"CHILD":[
                                                          {"NAME":"1"}
                                                          ,{"NAME":"2"}
                                                      ]},
                                                      {"ID": 3, "NAME": "Charlie", "AGE": 35,"CHILD":[
                                                          {"NAME":"1"}
                                                          ,{"NAME":"2"}
                                                      ]},
                                                      {"ID": 3, "NAME": "Charlie", "AGE": 35,"CHILD":[
                                                          {"NAME":"1"}
                                                          ,{"NAME":"2"}
                                                      ]},
                                                      {"ID": 3, "NAME": "Charlie", "AGE": 35,"CHILD":[
                                                          {"NAME":"1"}
                                                          ,{"NAME":"2"}
                                                      ]},
                                                      {"ID": 3, "NAME": "Charlie", "AGE": 35,"CHILD":[
                                                          {"NAME":"1"}
                                                          ,{"NAME":"2"}
                                                      ]},
                                                      {"ID": 3, "NAME": "Charlie", "AGE": 35,"CHILD":[
                                                          {"NAME":"1"}
                                                          ,{"NAME":"2"}
                                                      ]},
                                                      {"ID": 3, "NAME": "Charlie", "AGE": 35,"CHILD":[
                                                          {"NAME":"1"}
                                                          ,{"NAME":"2"}
                                                      ]},
                                                      {"ID": 3, "NAME": "Charlie", "AGE": 35,"CHILD":[
                                                          {"NAME":"1"}
                                                          ,{"NAME":"2"}
                                                      ]},
                                                      {"ID": 3, "NAME": "Charlie", "AGE": 35,"CHILD":[
                                                          {"NAME":"1"}
                                                          ,{"NAME":"2"}
                                                      ]},
                                                      {"ID": 3, "NAME": "Charlie", "AGE": 35,"CHILD":[
                                                          {"NAME":"1"}
                                                          ,{"NAME":"2"}
                                                      ]},
                                                      {"ID": 3, "NAME": "Charlie", "AGE": 35,"CHILD":[
                                                          {"NAME":"1"}
                                                          ,{"NAME":"2"}
                                                      ]},
                                                      {"ID": 3, "NAME": "Charlie", "AGE": 35,"CHILD":[
                                                          {"NAME":"1"}
                                                          ,{"NAME":"2"}
                                                      ]},
                                                      {"ID": 3, "NAME": "Charlie", "AGE": 35,"CHILD":[
                                                          {"NAME":"1"}
                                                          ,{"NAME":"2"}
                                                      ]},
                                                      {"ID": 3, "NAME": "Charlie", "AGE": 35,"CHILD":[
                                                          {"NAME":"1"}
                                                          ,{"NAME":"2"}
                                                      ]}
                                                  ]
                                              }
                """;
//        JsonPath jsonPath = JsonPath.compile();
//        System.out.println(JsonPath.read(jsonContent,"$").toString());
        JsonSchema jsonSchema = new JsonSchema(jsonContent);
        rootSchema.add("TEST", jsonSchema);
//        rootSchema.add("TEST", new JsonScannableTable(Sources.of(jsonContent)));

        // 3. 执行 SQL 查询
        try (Statement stmt = connection.createStatement()) {
            ResultSet rs = stmt.executeQuery("SELECT ID, NAME, AGE, CHILD FROM TEST.MY_TABLE WHERE AGE > 30");


            // 4. 输出结果
            while (rs.next()) {
                System.out.println("ID: " + rs.getInt("ID") +
                        ", Name: " + rs.getString("NAME") +
                        ", Age: " + rs.getInt("AGE")+
                        ", CHILD: " + ((ArrayList)rs.getObject("CHILD")).get(0));
            }
        }
    }

    // 自定义 JSON Schema
    static class JsonSchema extends AbstractSchema {
        private final String jsonContent;

        public JsonSchema(String jsonContent) {
            this.jsonContent = jsonContent;
        }

        @Override
        protected Map<String, Table> getTableMap() {
//            ListTransientTable
            return Collections.singletonMap("MY_TABLE", new JsonScannableTable(Sources.of(jsonContent)));

        }
    }

//    // 自定义 JSON 表
//    static class JsonTable extends AbstractTable {
//        private final String jsonContent;
//
//        public JsonTable(String jsonContent) {
//            this.jsonContent = jsonContent;
//        }
//
//        @Override
//        public RelDataType getRowType(RelDataTypeFactory typeFactory) {
//            return typeFactory.builder()
//                    .add("ID", typeFactory.createJavaType(Integer.class))
//                    .add("NAME", typeFactory.createJavaType(String.class))
//                    .add("AGE", typeFactory.createJavaType(Integer.class))
//                    .build();
//        }
//
//        public List<Object[]> getData() {
//            List<Object[]> rows = new ArrayList<>();
//            try {
//                ObjectMapper objectMapper = new ObjectMapper();
//                JsonNode arrayNode = objectMapper.readTree(jsonContent);
//                for (JsonNode node : arrayNode) {
//                    int ID = node.get("ID").asInt();
//                    String NAME = node.get("NAME").asText();
//                    int AGE = node.get("AGE").asInt();
//                    rows.add(new Object[]{ID, NAME, AGE});
//                }
//            } catch (IOException e) {
//                throw new RuntimeException("Failed to parse JSON content", e);
//            }
//            return rows;
//        }
//    }
}

